﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace Dashboard.Expense.Business
{
    public class Exp_ExpenseForm : BusinessBase
    {
        public DataTable QueryExpenseForm(string staffNo, DateTime fromDate, DateTime toDate, string types)
        {
            string sql = @"
            select h.ID,h.FormNo,h.FormName,h.StaffNo,h.DateFrom,h.DateTo,h.TotalExpense,h.AdvanceCash,
                h.Reimbursement,h.Comments,h.InsertDate,h.UpdatedDate 
                from exp_formhead h
                where h.Createuser = '{0}'
                and h.insertdate >= '{1}'
                and h.insertdate <= '{2}'
                {3}
                order by h.FormNo Desc
            ";

            string condition = "";
            if (!string.IsNullOrEmpty(types))
            {
                condition += string.Format(@"
                    and exists (
	                    select distinct d.formno from exp_formdetail d
	                     where d.formno = h.formno
	                       and d.apptypecode in ({0})
                    )
                ", types);
            }

            sql = string.Format(sql, staffNo, fromDate.ToString("yyyy-MM-dd"), toDate.ToString("yyyy-MM-dd"), condition);

            return DbConnection.DataSet(sql).Tables[0];
        }

        public void DeleteExpenseForm(string formNo)
        {
            string sql = "delete from exp_formhead where formno='" + formNo + "'";

            DbConnection.ExecuteSql(sql);

            sql = "delete from exp_formdetail where formno='" + formNo + "'";

            DbConnection.ExecuteSql(sql);
        }

        public int GetNextFormNo()
        {
            string sql = "select * from EXP_Parameter where ptype='FormNo' and pcode='FormNo'";
            DataTable param = DbConnection.DataSet(sql).Tables[0];
            if (param.Rows.Count == 0)
            {
                sql = "insert into EXP_Parameter values('FormNo','FormNo','费用单据表单号码','1',null,null)";
                DbConnection.ExecuteSql(sql);

                return 1;
            }
            else
            {
                int formNo = int.Parse(param.Rows[0]["pvalue1"].ToString()) + 1;
                sql = string.Format("update EXP_Parameter set pvalue1={0} where ptype='FormNo' and pcode='FormNo'", formNo);
                DbConnection.ExecuteSql(sql);

                return formNo;
            }
        }

        public int InsertForm(string sql, int formNo)
        {
            DbConnection.ExecuteSql(sql);

            string strSql = "select id from exp_formhead where formno='" + formNo + "'";
            return int.Parse(DbConnection.DataSet(strSql).Tables[0].Rows[0]["id"].ToString());
        }

        public void UpdateForm(string sql)
        {
            DbConnection.ExecuteSql(sql);
        }

        public DataTable GetFormHead(int id)
        {
            string sql = "select * from exp_formhead where id=" + id;
            return DbConnection.DataSet(sql).Tables[0];
        }

        public DataTable GetFormLines(int formNo)
        {
            string sql = "select * from exp_formdetail where formno='" + formNo + "' order by expensedate,apptypecode,feecode" ;
            return DbConnection.DataSet(sql).Tables[0];
        }

        public void DeleteExpenseItem(int id)
        {
            string sql = "delete from exp_formdetail where id=" + id;
            DbConnection.ExecuteSql(sql);
        }

        public void InsertExpenseItems(List<string> sqls, decimal totalAmount, int formID)
        {
            DbConnection.ExecuteSql(sqls);

            // 更新Head的Amount
            string sql = "update exp_formhead set totalexpense+={0},Reimbursement+={1} where id={2}";
            sql = string.Format(sql, totalAmount, totalAmount, formID);
            DbConnection.ExecuteSql(sql);
        }

        public DataTable QueryFormInfoForPrint(int id)
        {
            string sql = string.Format(@"
            select a.ID,a.FormNo,a.FormName,a.StaffNo,b.Name as StaffName,
	               b.Dept as Department,b.CostCenter,convert(nvarchar,a.InsertDate,111) as ReportDate,
	               a.Comments,a.TotalExpense,a.AdvanceCash,a.Reimbursement
               from exp_formhead a inner join namelist b on a.StaffNo = b.StaffNo
              WHERE a.ID = {0} ", id);

            return DbConnection.DataSet(sql).Tables[0];
        }

        public DataTable QueryFormLinesForPrint(int formNo)
        {
            string sql = string.Format(@"
            select a.ID,a.FormNo,convert(nvarchar,a.ExpenseDate,111) AS Date,a.AppTypeCode as ApplicationType,
            a.FeeCode as ExpenseType,a.Location,a.Amount,convert(decimal(18,2),ROUND(a.Amount * a.ExchangeRate,2)) as BaseAmount,
            a.Currency,convert(decimal(18,2),ROUND(a.ExchangeRate,2)) as ExchangeRate,case when a.HasInvoice='1' then 'Y' else 'N' end AS ReceiptStatus, a.Comments
            from exp_formdetail a
            where a.FormNo = '{0}'
            order by a.ExpenseDate ", formNo);

            return DbConnection.DataSet(sql).Tables[0];
        }
    }
}
